﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace QueryInsertUpdate
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public void OpenGridView()
        {
            Form2 form2 = new Form2();
            DialogResult res = form2.ShowDialog(this);

            if (res == DialogResult.OK)
            {
                //MessageBox.Show("Ga", "Ga");
                this.RefreshComboBox();
                this.Query();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.RefreshComboBox();
        }

        private void Query()
        {
            string org, speed, direct, name, location;
            //MessageBox.Show("Start query!", "Info");

            if (orgComboBox.Text == "All")
            {
                org = "%%";
            }
            else
            {
                org = orgComboBox.Text;
            }

            if (speedComboBox.Text == "All")
            {
                speed = "%%";
            }
            else
            {
                speed = speedComboBox.Text;
            }

            if (directionComboBox.Text == "All")
            {
                direct = "%%";
            }
            else
            {
                direct = directionComboBox.Text;
            }

            if (nameTextBox.Text.Length == 0)
            {
                name = "%%";
            }
            else
            {
                name = "%" + nameTextBox.Text + "%";
            }

            if (locationTextBox.Text.Length == 0)
            {
                location = "%%";
            }
            else
            {
                location = "%" + locationTextBox.Text + "%";
            }
            //MessageBox.Show(org + " - " + speed + " - " + direct, "Info");
            this.vms_vnpTableAdapter.Fill(this.QueryInsertUpdateDataSet.vms_vnp, org, speed, direct, name, location);
        }

        public void RefreshComboBox()
        {
            // TODO: This line of code loads data into the 'QueryInsertUpdateDataSet.vms_vnp3' table. You can move, or remove it, as needed.
            this.vms_vnp3TableAdapter.Fill(this.QueryInsertUpdateDataSet.vms_vnp3);
            // TODO: This line of code loads data into the 'QueryInsertUpdateDataSet.vms_vnp2' table. You can move, or remove it, as needed.
            this.vms_vnp2TableAdapter.Fill(this.QueryInsertUpdateDataSet.vms_vnp2);
            // TODO: This line of code loads data into the 'QueryInsertUpdateDataSet.vms_vnp1' table. You can move, or remove it, as needed.
            this.vms_vnp1TableAdapter.Fill(this.QueryInsertUpdateDataSet.vms_vnp1);
            // TODO: This line of code loads data into the 'QueryInsertUpdateDataSet.vms_vnp' table. You can move, or remove it, as needed.
        }

        private void QueryButton_Click(object sender, EventArgs e)
        {
            this.Query();
        }

        private void ImportButton_Click(object sender, EventArgs e)
        {
            OpenFileDialog BrowseImport = new OpenFileDialog();
            BrowseImport.Filter = "Excel Files (*.xls; *.xlsx)|*.xls; *.xlsx";
            BrowseImport.FilterIndex = 1;
            BrowseImport.Multiselect = false;

            DialogResult userClickedOK = BrowseImport.ShowDialog();
            if (userClickedOK == DialogResult.OK)
            {
                MessageBox.Show(BrowseImport.FileName, "Info");
/*
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;

                //string str;
                double number = 0;
                int rCnt = 0;
                int cCnt = 0;
                string name = "";
                string organization = "";
                string end2end = "";
                string speed = "";
                string direction = "";

                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Open(BrowseImport.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                range = xlWorkSheet.UsedRange;
                if (range.Rows.Count == 0)
                {
                    MessageBox.Show("File không có dữ liệu!", "Thông báo");
                }
                else
                {
                    if (range.Columns.Count < 6)
                    {
                        MessageBox.Show("Cấu trúc file không đúng!", "Thông báo");
                    }
                    else
                    {
                        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                        {
                            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                            {
                                switch (cCnt)
                                {
                                    case 1:
                                        end2end = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                    case 2:
                                        name = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                    case 3:
                                        speed = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                    case 4:
                                        direction = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                    case 5:
                                        organization = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                    case 6:
                                        number = (double)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                        break;
                                }
                            }

                            //FlowManageDataSetTableAdapters.VNP_VMS_FLOWTableAdapter InsertData;
                            //InsertData = new FlowManageDataSetTableAdapters.VNP_VMS_FLOWTableAdapter();
                            
                            try
                            {
                                InsertData.InsertQuery((decimal)number, name, speed, direction, organization, end2end);
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.Message, "Insert Failed");
                            }
                            
                        }
                
                        MessageBox.Show("Import thành công" + rCnt.ToString() + " dòng!", "Thông báo");

                        xlWorkBook.Close(true, null, null);
                        xlApp.Quit();
                    }
                }
*/
            }
        }

        private void InsertButton_Click(object sender, EventArgs e)
        {
            //System.Threading.Thread GridView = new System.Threading.Thread(new System.Threading.ThreadStart(OpenGridView));
            //GridView.Start();
            this.OpenGridView();
            
        }

        private void DeleteButton_Click(object sender, EventArgs e)
        {
            if (this.vms_vnpDataGridView.SelectedRows.Count == 0)
            {
                MessageBox.Show("Hãy chọn dòng để xóa!", "Thông báo");
            }
            else
            {
                DialogResult deleteYN = MessageBox.Show("Bạn thật sự muốn xóa dữ liệu?", "Thông báo", MessageBoxButtons.YesNo);
                if (deleteYN == DialogResult.Yes)
                {
                    System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection();
                    sqlConnection1.ConnectionString = @"Data Source=.\SQLEXPRESS;
                                                        AttachDbFilename=|DataDirectory|\QueryInsertUpdate.mdf;
                                                        Integrated Security=True;
                                                        User Instance=True";
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = "UPDATE vms_vnp SET [active] = 'N' WHERE [name] = @name";
                    cmd.Connection = sqlConnection1;
                    sqlConnection1.Open();

                    for (int i = 0; i < this.vms_vnpDataGridView.SelectedRows.Count; i++)
                    {
                        cmd.Parameters.AddWithValue("@name", vms_vnpDataGridView.SelectedRows[i].Cells[1].Value.ToString());
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.RemoveAt("@name");
                    }

                    sqlConnection1.Close();
                    this.Query();
                    this.RefreshComboBox();
                }
            }
        }
    }
}
/*
            System.Threading.Thread GridView = new System.Threading.Thread(new System.Threading.ThreadStart(OpenGridView));
            GridView.Start();
            this.Close();
*/